/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package src.services.customer;

/**
 *
 * @author Yusup Jauhari Shandi <in.visi>
 */

import java.math.BigDecimal;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.primefaces.model.SortOrder;
import src.HibernateUtil;
import src.domain.customer.Corbod;
import src.domain.customer.Corbuslic;
import src.domain.customer.Customer;
import src.services.CRUEDService;
import src.util.Utils;


public class CustomerModel implements  java.io.Serializable{
      private CRUEDService service;

    public CustomerModel() {
        service = new CRUEDService();
    }

    public CRUEDService getService() {
        return service;
    }

    public void setService(CRUEDService service) {
        this.service = service;
    }
    
    public Customer getById(Integer id) {
        return (Customer)  service.getById(Customer.class, id);
    }
    
    public Customer getByCif(String cif) {
        Map<String, String> where = new HashMap<>();
        where.put("cust.cif", cif);
        return (Customer) service.getObjectSQL("from Customer cust", where);
    }
    
//    public boolean isCIFExist(String cif) {
//        BigDecimal count;
//         try { count = (BigDecimal) service.getObjectSQLdirect("select count(*) from customer where cif = "+cif);
//        } catch (RuntimeException e) {count = BigDecimal.ZERO;}
//                
//        return (count.compareTo(BigDecimal.ZERO)==0 ? false : true);
//    }
    
    
    public boolean isCIFExist(String value, Integer id) {
        BigDecimal count;
        
        String cond = " where cif = "+Utils.fmtSQLString(value);
        if (id != null) cond+=" and customer_id <> "+id;
        
        try { count = (BigDecimal) service.getObjectSQLdirect("select count(*) from customer "+cond);
        } catch (RuntimeException e) {count = BigDecimal.ZERO;}
        
        return (count.compareTo(BigDecimal.ZERO)==0 ? false : true);
    }
    
    public List<Corbod> loadCorbod(Integer id) {
        
        List<Corbod> hasil = new ArrayList<>();
        List<Object[]> temp;
        
            Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            org.hibernate.Transaction tx = session.beginTransaction();
            try {

                String sql =
                "SELECT CUSTOMER_ID,SEQ, DIRECTOR_NAME, JOB_TITLE, IDCARD_NO, NATIONALITY_ID FROM CORBOD "+
                "WHERE CUSTOMER_ID = :id";
        
                Query q = session.createSQLQuery(sql).setParameter("id", id);
                
                temp = (List) q.list();
                for (Object[] ob : temp) {
                    Corbod sav = new Corbod();
                    
                                                                               
                    //SavprodrateId detailId = new SavprodrateId();
                    //detailId.setSavproductId(Integer.valueOf(SAVPRODUCT_ID.intValue()));
                    //detailId.setSeq(Integer.valueOf(SEQ.intValue()));
                    
                    //sav.setId(detailId);
                    //Integer nationalityId =((BigDecimal)ob[5]).intValue();
                   sav.setNationalityId(((BigDecimal)ob[5]).intValue());
                  //  sav.setNationality((new NationalityModel()).getById(nationalityId));
                    sav.setDirectorName(String.valueOf(ob[2]));
                    sav.setJobTitle(String.valueOf(ob[3]));
                    sav.setIdcardNo(String.valueOf(ob[4]));
                    hasil.add(sav);
                }
            
                tx.commit();

            } catch (RuntimeException e) {

                if (tx != null && tx.isActive()) {
                    try {tx.rollback(); } 
                    catch (HibernateException e1) {}
                    throw e;
                }
            }

            finally {}
                
        return hasil;
    }
    
    public List<Corbuslic> loadCorbuslic(Integer id) {
        
        List<Corbuslic> hasil = new ArrayList<>();
        List<Object[]> temp;
        
            Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            org.hibernate.Transaction tx = session.beginTransaction();
            try {

                String sql =
                "SELECT CUSTOMER_ID,SEQ, BUSLIC_ID, BUSLIC_NO, EXPIRY_DATE FROM CORBUSLIC "+
                "WHERE CUSTOMER_ID = :id";
        
                Query q = session.createSQLQuery(sql).setParameter("id", id);
                
                temp = (List) q.list();
                for (Object[] ob : temp) {
                    Corbuslic sav = new Corbuslic();
                    
                                                                               
                    //SavprodrateId detailId = new SavprodrateId();
                    //detailId.setSavproductId(Integer.valueOf(SAVPRODUCT_ID.intValue()));
                    //detailId.setSeq(Integer.valueOf(SEQ.intValue()));
                    
                    //sav.setId(detailId);
                    Integer buslicId =((BigDecimal)ob[2]).intValue();
                    sav.setBuslic((new BuslicModel()).getById(buslicId));
                    sav.setBuslicNo(String.valueOf(ob[3]));
                    sav.setBuslicId(buslicId);
                    sav.setExpiryDate((Date)ob[4]);
                    hasil.add(sav);
                }
            
                tx.commit();

            } catch (RuntimeException e) {

                if (tx != null && tx.isActive()) {
                    try {tx.rollback(); } 
                    catch (HibernateException e1) {}
                    throw e;
                }
            }

            finally {}
                
        return hasil;
    }
    
    public List<Customer> loadLazy(int start, int end, String sortField, SortOrder sortOrder, Map<String, String> filters) {
        
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
        List<Customer> result = new ArrayList<>();
        List<Object[]> tmp = null;
        
        /*
customerId, cif, customerName, aliasName, idAddressLine1, idPostcode, idcardNo, npwp
,phone, fax, mobilePhone, email, pin, fingerPrint, specimen
         */
        
        try {
            
            String where = "";
            if (filters!=null) {
                for (Map.Entry<String, String> e : filters.entrySet()) {
                    if (e.getValue()!=null)
                        where += (where.length()>0 ? " AND " : "") + " lower(" + e.getKey() + ") like lower('%" + e.getValue() + "%')";
                }    
            }
            
            String orderBy = "";
            if (sortField!=null && sortOrder==null)
                orderBy = " order by "+sortField+" "+sortOrder;
                        
           // String hql = "from Customer "+ (where.length() > 0 ? " where " +  where : "")+orderBy;
            String hql = 
                    "select customerId, cif, customerName, idAddressLine1 from Customer "+ (where.length() > 0 ? " where " +  where : "")+orderBy;
            
            Query q = session.createQuery(hql).setFirstResult(start).setMaxResults(end);
            tmp = (List) q.list();
            
            for (Object[] ob : tmp) {
                Customer cust = new Customer();
                cust.setCustomerId((Integer) ob[0]);
                cust.setCif((String) ob[1]);
                cust.setCustomerName((String) ob[2]);
                cust.setIdAddressLine1((String) ob[3]);
                result.add(cust);
            }
            
            tx.commit();
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {tx.rollback(); } 
                catch (HibernateException e1) {}
            }
            Logger.getLogger(CustomerModel.class.getName()).log(Level.SEVERE, null, e);
        }
        
        finally {}
        return result;
    }
    
    public boolean saveData(Customer  data) {
        return this.service.saveData(data);
    }
}
